package org.uds.android.fitdroid.demo.apis;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

/** Helper to the database, manages versions and creation */
public class EventDataSQLHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "fitdroid";
	private static final int DATABASE_VERSION = 1;

	// Table name
	public static final String GPSDATA = "gpsdata";
	public static final String TCONSITS = "tconsists";
	public static final String TRACK = "track";

	// Columns
	public static final String LATITUDE = "latitude";
	public static final String LONGITUDE = "longitude";
	public static final String ALTITUDE = "altitude";
	public static final String DATE = "date";
	public static final String TIME = "time";

	public EventDataSQLHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table " + GPSDATA + "( " + BaseColumns._ID
				+ " integer primary key autoincrement, " + LATITUDE + " varchar(30), "
				+ LONGITUDE + " varchar(30), "+ ALTITUDE + " varchar(30), "+ DATE + " varchar(30), "+ TIME + " varchar(30));";
		Log.d("GPSData", "onCreate: " + sql);
		db.execSQL(sql);
		
		sql ="create table " + TCONSITS + " (_id integer primary key autoincrement, trackid integer, gpsdataid integer)";
		db.execSQL(sql);
		
		sql ="CREATE TRIGGER delete_GPSDATA DELETE ON "+GPSDATA
			 + " BEGIN "
		     +		"DELETE FROM "+TCONSITS+" WHERE gpsdataid = old._id; "
		     + "END;";
		db.execSQL(sql);
		
		
		sql ="create table " + TRACK + " (_id integer primary key autoincrement, name varchar(30), difficulty varchar(30))";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

		String sql = null;
		if (oldVersion == 1) 
			sql = "alter table " + GPSDATA + " add note text;";
		if (oldVersion == 2)
			sql = "";

		Log.d("EventsData", "onUpgrade	: " + sql);
		if (sql != null)
			db.execSQL(sql);
	}

}
